#include "dbexportwidget.h"
#include <QtXlsx/QtXlsx>

dbExportWidget::dbExportWidget(QWidget *parent) : QWidget(parent)
{
    //m_pSqlQuery = new QSqlQuery();

    //变量初始化
    m_dbCommName = QLatin1String("qt_sql_default_connection");   //设置数据库连接名称
    m_dbTableName = QLatin1String("*");  //数据库表名称
    m_dbColumnsSql = QLatin1String("*"); //查询列名称
    m_dbOrderSql = QLatin1String("");   //排序条件
    m_dbWhereSql = QLatin1String("");   //查询条件
}

void dbExportWidget::setDbInfo(const QString &dbFileName, const QString &dbCommName)
{
    setDbFileName(dbFileName);
    setDbCommName(dbCommName);
}

void dbExportWidget::setDbQueryInfo(const QString &dbTableName, const QString &dbColumnsSql, const QString &dbWhereSql, const QString &dbOrderSql)
{
    setDbTableName(dbTableName);
    setDbColumnsSql(dbColumnsSql);
    setDbWhereSql(dbWhereSql);
    setDbOrderSql(dbOrderSql);
}

void dbExportWidget::setDbFileName(const QString &dbFileName)
{
    m_dbFileName = dbFileName;
}

QString dbExportWidget::dbCommName() const
{
    return m_dbCommName;
}

void dbExportWidget::setDbCommName(const QString &dbCommName)
{
    m_dbCommName = dbCommName;
}

void dbExportWidget::setDbTableName(const QString &dbTableName)
{
    m_dbTableName = dbTableName;
}

void dbExportWidget::setDbColumnsSql(const QString &dbColumnsSql)
{
    m_dbColumnsSql = dbColumnsSql;
}

void dbExportWidget::setDbOrderSql(const QString &dbOrderSql)
{
    m_dbOrderSql = dbOrderSql;
}

void dbExportWidget::setDbWhereSql(const QString &dbWhereSql)
{
    m_dbWhereSql = dbWhereSql;
}

void dbExportWidget::setExcelFileName(const QString fileName)
{
    m_ExcelFileName = fileName;
}

void dbExportWidget::setExcelRowHeigth(const int height)
{
    m_ExcelRowHeight = height;
}

void dbExportWidget::setExcelHeaderHeight(const int height)
{
    m_ExcelHeaderHeight = height;
}

void dbExportWidget::setExcelColumNameList(const QList<QString> &ColumnNamesList)
{
    m_ColumnNamesList = ColumnNamesList;
}

void dbExportWidget::setExcelColumnWidthList(const QList<int> &ColumnWidthList)
{
    m_ColumnWidthList = ColumnWidthList;
}

int dbExportWidget::exportToExcel()
{
    // 获取数据库
    if(!QSqlDatabase::contains(m_dbCommName))
    {
        m_Database = QSqlDatabase::addDatabase("QSQLITE", m_dbCommName);
        m_Database.setDatabaseName(m_dbFileName);
    }

    m_Database = QSqlDatabase::database(m_dbCommName);

    if(!m_Database.isOpen())
    {
        m_Database.open();
    }

    //执行查询
    m_sqlString = QString("select %1 from %2 %3 order by %4;")
            .arg(m_dbColumnsSql)
            .arg(m_dbTableName)
            .arg(m_dbWhereSql)
            .arg(m_dbOrderSql);

    QSqlQuery query(m_Database);
    query.exec(m_sqlString);
    QSqlRecord rec = query.record();

    QXlsx::Document doc;
    QXlsx::Format headerFormat;
    headerFormat.setFontBold(true);
    headerFormat.setHorizontalAlignment(QXlsx::Format::AlignHCenter);

    for(int i=0; i<m_ColumnWidthList.count(); i++)
    {
        doc.setColumnWidth(i+1, m_ColumnWidthList.at(i));
    }

//    doc.setColumnWidth(1,2,25);
//    doc.setColumnWidth(3,6,15);

    for(int i=0; i<m_ColumnNamesList.count(); i++)
    {
        doc.write(1, i+1, m_ColumnNamesList.at(i), headerFormat);
    }

    int columnCount = rec.count();
    int rowIdx  = 2;
    while(query.next())
    {
        for(int i=0; i<columnCount; i++)
        {
            doc.write(rowIdx, i+1, query.value(i));
        }
        rowIdx++;
    }

    doc.saveAs(m_ExcelFileName);

    return 0;
}

int dbExportWidget::exportToExcel(const QString fileName)
{
    setExcelFileName(fileName);
    return exportToExcel();
}

